using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace MvcExtExample.Handlers
{
    public partial class PagingRequest : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            int start = Convert.ToInt32(Request["start"].ToString());   // ExtJS Paging 必须指定的参数（从第几行记录开始）/ ExtJS默认0是第一行记录
            int limit = Convert.ToInt32(Request["limit"].ToString()); // ExtJS Paging 必须指定的参数（每页显示多少行记录）
            framework.JSONHelper jsonHelp = new framework.JSONHelper();
            int TotalRecords = 0; 
            DataSet DSet = GET_Product_Data(start, limit, out TotalRecords); // 获取数据
            jsonHelp.success = true;
            jsonHelp.totlalCount = TotalRecords;// 记录总数
            if (DSet != null)
            {
                DataTable DTable = DSet.Tables[0];
                for (int i = 0; i < DTable.Rows.Count; i++)
                {
                    // 循环生成JSON代码
                    jsonHelp.AddItem("Well_ID", DTable.Rows[i]["Well_ID"].ToString());
                    jsonHelp.AddItem("Well_Type_ID", DTable.Rows[i]["Well_Type_ID"].ToString());
                    jsonHelp.AddItem("Dep_ID", DTable.Rows[i]["Dep_ID"].ToString());
                    jsonHelp.AddItem("Status", DTable.Rows[i]["Status"].ToString());
                    jsonHelp.ItemOk();
                }
            }
            Response.Write(jsonHelp.ToString());  // 输出JSON代码
        }
        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="PageIndex">记录索引</param>
        /// <param name="PageSize">每页显示记录数</param>
        /// <param name="TotalRecords">总记录数</param>
        public DataSet GET_Product_Data(int start, int limit,out int TotalRecords)
        {
            SqlConnection _Connection = null;
            SqlDataAdapter _Adapter = null;
            DataSet DSet = null;
            try
            {
                _Connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
                _Adapter = new SqlDataAdapter("SELECT [Well_ID] FROM [Tb_CommonWell] ",_Connection);
                DSet = new DataSet();
                _Adapter.Fill(DSet);
                TotalRecords = Convert.ToInt32(DSet.Tables[0].Rows.Count);      // 记录总数
                if (TotalRecords < 1) return null;  // 没有记录
                int pageLowerBound = start+1;                                            // 从第几条数据开始
                int pageUpperBound = pageLowerBound + limit;             // 每页多少条数据
                StringBuilder sb = new StringBuilder();
                if (TotalRecords >= pageLowerBound)
                {
                    for (int i = pageLowerBound; i < TotalRecords && i < pageUpperBound; i++)
                    {
                        sb.AppendFormat("'{0}',", DSet.Tables[0].Rows[i-1][0].ToString());//构造ID in() 条件,取其中一页
                    }
                }
                else 
                    return null; // 没有记录
                if (sb.Length > 1)
                    sb.Remove(sb.Length - 1, 1);//删除最后一个逗号
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT a.[Well_ID],a.[Well_Type_ID],a.[Dep_ID],a.[Status]  ");
                strSql.Append(" FROM [Tb_CommonWell] as a left join Tb_Well_Type  as b on a.Well_Type_ID = b.Well_Type_ID ");
                strSql.AppendFormat(" where a.[Well_ID] in({0})", sb.ToString());
                _Adapter = new SqlDataAdapter(strSql.ToString(), _Connection);
                DSet = new DataSet();
                _Adapter.Fill(DSet);
            }
            finally
            {
                if (_Connection != null && _Connection.State == ConnectionState.Open)
                {
                    _Connection.Close();
                }
            }
            return DSet;
        }
    }
}